import 'package:flutter_4/model/UserInfo.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

final String tableUserInfo = 'userInfo';
final String columnId = 'id';
final String columnUserAccount = 'userAccount';
final String columnPassword = 'password';

/// sqliteHelper
class SQLiteHelper {

  Database db;

  openSqlite() async {
    // 获取数据库文件的存储路径
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'userInfo.db');

    //根据数据库文件路径和数据库版本号创建数据库表
    db = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
          await db.execute('''
          CREATE TABLE $tableUserInfo (
            $columnId INTEGER PRIMARY KEY, 
            $columnUserAccount TEXT, 
            $columnPassword TEXT )
          ''');
        });
  }

  // 插入一条个人信息数据
  Future<UserInfo> insert(UserInfo userInfo) async {
    userInfo.id = await db.insert(tableUserInfo, userInfo.toJson());
    return userInfo;
  }

  // 查找所有个人信息
  Future<List<UserInfo>> queryAll() async {
    List<Map> maps = await db.query(tableUserInfo, columns: [
      columnId,
      columnUserAccount,
      columnPassword,
    ]);

    if (maps == null || maps.length == 0) {
      return null;
    }

    List<UserInfo> userInfos = [];
    for (int i = 0; i < maps.length; i++) {
      userInfos.add(UserInfo.fromJson(maps[i]));
    }
    return userInfos;
  }

  // 根据ID查找个人信息
  Future<UserInfo> getUserInfo(int id) async {
    List<Map> maps = await db.query(tableUserInfo,
        columns: [
          columnId,
          columnUserAccount,
          columnPassword,
        ],
        where: '$columnId = ?',
        whereArgs: [id]);
    if (maps.length > 0) {
      return UserInfo.fromJson(maps.first);
    }
    return null;
  }


  // 根据用户账号（昵称邮箱）查找个人信息
  Future<UserInfo> getUserInfoByAccount(String userAccount) async {
    List<Map> maps = await db.query(tableUserInfo,
        columns: [
          columnId,
          columnUserAccount,
          columnPassword,
        ],
        where: '$columnUserAccount = ?',
        whereArgs: [userAccount]);
    if (maps.length > 0) {
      return UserInfo.fromJson(maps.first);
    }
    return null;
  }


  // 根据ID删除个人信息
  Future<int> delete(int id) async {
    return await db.delete(tableUserInfo, where: '$columnId = ?', whereArgs: [id]);
  }

  // 更新个人信息
  Future<int> update(UserInfo userInfo) async {
    return await db.update(tableUserInfo, userInfo.toJson(),
        where: '$columnId = ?', whereArgs: [userInfo.id]);
  }

  // 根据账号更新个人信息
  Future<int> updateByAccount(UserInfo userInfo) async {
    return await db.update(tableUserInfo, userInfo.toJson(),
        where: '$columnUserAccount = ?', whereArgs: [userInfo.userAccount]);
  }

  // 及时关闭数据库，防止内存泄漏
  close() async {
    await db.close();
  }
}